//订单项接口
var express = require('express');
var router = express.Router();
const mysql = require('mysql2/promise');
// 导入连接数据库key
const pool = require('../MySQLkey/kry');
// 导入工具类
const tools = require('../tool/tool');

/* GET order_items listing. */
//获取全部订单项信息 - 支持分页、筛选、排序
router.get('/', async function(req, res) {
  let connection;
  try {
    connection = await pool.getConnection();

    // 获取查询参数
    const {
      order_id,
      product_id,
      page = 1,
      limit = 10,
      sort_by = 'created_at',
      sort_order = 'DESC'
    } = req.query;

    // 验证分页参数
    const pagination = tools.validatePagination(page, limit, 100); // 最大100条

    // 构建查询条件
    let whereConditions = [];
    let params = [];

    // 订单ID筛选
    if (order_id) {
      if (!tools.validateUserId(order_id)) {
        return tools.handleError(res, null, '订单ID格式不正确', 400);
      }
      whereConditions.push('order_id = ?');
      params.push(parseInt(order_id));
    }

    // 商品ID筛选
    if (product_id) {
      if (!tools.validateUserId(product_id)) {
        return tools.handleError(res, null, '商品ID格式不正确', 400);
      }
      whereConditions.push('product_id = ?');
      params.push(parseInt(product_id));
    }

    // 验证排序字段
    const allowedSortFields = ['id', 'order_id', 'product_id', 'price', 'quantity', 'total_amount', 'created_at'];
    const sortField = allowedSortFields.includes(sort_by) ? sort_by : 'created_at';
    const sortDirection = sort_order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

    // 构建基础查询语句
    let baseQuery = `
      SELECT
        oi.id, oi.order_id, oi.product_id, oi.product_name,
        oi.product_image, oi.product_specs, oi.price, oi.quantity,
        oi.total_amount, oi.created_at,
        o.order_no, o.order_status,
        p.name as current_product_name, p.main_image as current_product_image, p.status as product_status
      FROM order_items oi
      LEFT JOIN orders o ON oi.order_id = o.id
      LEFT JOIN products p ON oi.product_id = p.id
    `;

    // 添加WHERE条件
    if (whereConditions.length > 0) {
      baseQuery += ' WHERE ' + whereConditions.join(' AND ');
    }

    // 获取总数
    let countQuery = 'SELECT COUNT(*) as total FROM order_items oi';
    if (whereConditions.length > 0) {
      countQuery += ' WHERE ' + whereConditions.join(' AND ');
    }

    const [countResult] = await connection.query(countQuery, params);
    const total = countResult[0].total;

    // 添加排序和分页
    baseQuery += ` ORDER BY oi.${sortField} ${sortDirection} LIMIT ? OFFSET ?`;
    params.push(pagination.limit, pagination.offset);

    // 执行查询
    const [rows] = await connection.query(baseQuery, params);

    // 处理product_specs JSON字段
    const processedRows = rows.map(row => ({
      ...row,
      product_specs: row.product_specs ? JSON.parse(row.product_specs) : null
    }));

    // 返回结果
    tools.handleSuccess(res, {
      order_items: processedRows,
      pagination: {
        current_page: pagination.page,
        per_page: pagination.limit,
        total: total,
        total_pages: Math.ceil(total / pagination.limit)
      }
    }, '订单项列表获取成功');

  } catch (err) {
    tools.handleError(res, err, '获取订单项列表失败');
  } finally {
    if (connection) connection.release();
  }
});

/* GET order_items by order ID */
//根据订单ID获取订单项列表
router.get('/order/:order_id', async function(req, res) {
  let connection;
  try {
    connection = await pool.getConnection();

    const orderId = req.params.order_id;
    const {
      page = 1,
      limit = 20,
      sort_by = 'id',
      sort_order = 'ASC'
    } = req.query;

    // 验证订单ID
    if (!tools.validateUserId(orderId)) {
      return tools.handleError(res, null, '订单ID格式不正确', 400);
    }

    // 验证分页参数
    const pagination = tools.validatePagination(page, limit, 50);

    // 验证排序字段
    const allowedSortFields = ['id', 'product_id', 'price', 'quantity', 'total_amount'];
    const sortField = allowedSortFields.includes(sort_by) ? sort_by : 'id';
    const sortDirection = sort_order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

    // 首先检查订单是否存在
    const [orderCheck] = await connection.query(
      'SELECT id, order_no, order_status FROM orders WHERE id = ?',
      [orderId]
    );

    if (orderCheck.length === 0) {
      return tools.handleError(res, null, '订单不存在', 404);
    }

    // 查询订单项详情
    const itemsQuery = `
      SELECT
        oi.id, oi.order_id, oi.product_id, oi.product_name,
        oi.product_image, oi.product_specs, oi.price, oi.quantity,
        oi.total_amount, oi.created_at,
        p.name as current_product_name, p.main_image as current_product_image,
        p.status as product_status, p.stock as current_stock
      FROM order_items oi
      LEFT JOIN products p ON oi.product_id = p.id
      WHERE oi.order_id = ?
      ORDER BY oi.${sortField} ${sortDirection}
      LIMIT ? OFFSET ?
    `;

    // 获取总数
    const [countResult] = await connection.query(
      'SELECT COUNT(*) as total FROM order_items WHERE order_id = ?',
      [orderId]
    );
    const total = countResult[0].total;

    // 执行查询
    const [itemsRows] = await connection.query(itemsQuery, [
      orderId,
      pagination.limit,
      pagination.offset
    ]);

    // 处理product_specs JSON字段
    const processedItems = itemsRows.map(item => ({
      ...item,
      product_specs: item.product_specs ? JSON.parse(item.product_specs) : null
    }));

    // 计算订单项统计信息
    const [statsResult] = await connection.query(`
      SELECT
        COUNT(*) as total_items,
        SUM(quantity) as total_quantity,
        SUM(total_amount) as total_amount
      FROM order_items
      WHERE order_id = ?
    `, [orderId]);

    const stats = statsResult[0];

    // 返回结果
    tools.handleSuccess(res, {
      order_info: orderCheck[0],
      order_items: processedItems,
      statistics: {
        total_items: stats.total_items,
        total_quantity: stats.total_quantity,
        total_amount: parseFloat(stats.total_amount || 0)
      },
      pagination: {
        current_page: pagination.page,
        per_page: pagination.limit,
        total: total,
        total_pages: Math.ceil(total / pagination.limit)
      }
    }, '订单项详情获取成功');

  } catch (err) {
    tools.handleError(res, err, '获取订单项详情失败');
  } finally {
    if (connection) connection.release();
  }
});

/* POST create new order item */
// 创建订单项
router.post('/', async function(req, res) {
  let connection;
  try {
    connection = await pool.getConnection();

    // 获取请求数据
    const {
      order_id,
      product_id,
      product_name,
      product_image,
      product_specs,
      price,
      quantity,
      total_amount
    } = req.body;

    // 验证必需字段
    if (!order_id || !product_id || !product_name || !price || !quantity || !total_amount) {
      return res.status(400).json({
        success: false,
        message: '订单ID、商品ID、商品名称、价格、数量和小计金额为必填项'
      });
    }

    // 验证数据格式
    const orderId = parseInt(order_id);
    const productId = parseInt(product_id);
    const itemPrice = parseFloat(price);
    const itemQuantity = parseInt(quantity);
    const itemTotalAmount = parseFloat(total_amount);

    if (isNaN(orderId) || orderId <= 0) {
      return res.status(400).json({
        success: false,
        message: '订单ID格式不正确'
      });
    }

    if (isNaN(productId) || productId <= 0) {
      return res.status(400).json({
        success: false,
        message: '商品ID格式不正确'
      });
    }

    if (isNaN(itemPrice) || itemPrice <= 0) {
      return res.status(400).json({
        success: false,
        message: '商品价格必须大于0'
      });
    }

    if (isNaN(itemQuantity) || itemQuantity <= 0) {
      return res.status(400).json({
        success: false,
        message: '商品数量必须大于0'
      });
    }

    if (isNaN(itemTotalAmount) || itemTotalAmount <= 0) {
      return res.status(400).json({
        success: false,
        message: '小计金额必须大于0'
      });
    }

    // 验证小计金额计算是否正确
    const calculatedTotal = itemPrice * itemQuantity;
    if (Math.abs(calculatedTotal - itemTotalAmount) > 0.01) {
      return res.status(400).json({
        success: false,
        message: '小计金额计算错误，应为: ' + calculatedTotal.toFixed(2)
      });
    }

    // 验证订单是否存在
    const [orderCheck] = await connection.query(
      'SELECT id FROM orders WHERE id = ?',
      [orderId]
    );

    if (orderCheck.length === 0) {
      return res.status(400).json({
        success: false,
        message: '订单不存在'
      });
    }

    // 验证商品是否存在
    const [productCheck] = await connection.query(
      'SELECT id, name, price, stock FROM products WHERE id = ? AND status = 1',
      [productId]
    );

    if (productCheck.length === 0) {
      return res.status(400).json({
        success: false,
        message: '商品不存在或已下架'
      });
    }

    const product = productCheck[0];

    // 检查库存是否充足
    if (product.stock < itemQuantity) {
      return res.status(400).json({
        success: false,
        message: `商品库存不足，当前库存: ${product.stock}`
      });
    }

    // 处理商品规格JSON
    let productSpecsJson = null;
    if (product_specs) {
      try {
        productSpecsJson = typeof product_specs === 'string'
          ? product_specs
          : JSON.stringify(product_specs);
      } catch (e) {
        return res.status(400).json({
          success: false,
          message: '商品规格格式错误'
        });
      }
    }

    // 插入订单项数据
    const insertQuery = `
      INSERT INTO order_items (
        order_id, product_id, product_name, product_image,
        product_specs, price, quantity, total_amount
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `;

    const [result] = await connection.query(insertQuery, [
      orderId,
      productId,
      product_name,
      product_image || null,
      productSpecsJson,
      itemPrice,
      itemQuantity,
      itemTotalAmount
    ]);

    // 更新商品库存
    await connection.query(
      'UPDATE products SET stock = stock - ? WHERE id = ?',
      [itemQuantity, productId]
    );

    // 返回创建的订单项ID
    res.status(201).json({
      success: true,
      message: '订单项创建成功',
      data: {
        id: result.insertId,
        order_id: orderId,
        product_id: productId,
        quantity: itemQuantity,
        total_amount: itemTotalAmount
      }
    });

  } catch (err) {
    console.error('创建订单项失败:', err);
    res.status(500).json({
      success: false,
      message: '创建订单项失败',
      error: err.message
    });
  } finally {
    if (connection) connection.release();
  }
});

module.exports = router;
